package com.zp.dao.impl;

import com.zp.dao.UserDao;
import com.zp.domain.User;
import com.zp.domain.vo.UserVo;
import com.zp.utils.DbHelper;
import com.zp.utils.DruidUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * 此类为上个版本老师要求，使用DBHelper 和 JdbcUtil 工具类的 原生JDBC
 * 使用了 Druid 后，发现修改 UserDao 时 实现接口函数过于麻烦，遂解除接口实现
 */
//public class UserDaoRaw implements UserDao {
public class UserDaoRaw {

    private DbHelper dbHelper;

    public UserDaoRaw() {
        dbHelper = new DbHelper();
    }

//    @Override
    public User getUserBySno(String sno) {
        String sql = "select * from user where sno = ?";
        List<Object> params = new ArrayList<>();
        User user = null;
        params.add(sno);
        try {
            ResultSet rs = dbHelper.executeQuery(sql, params);
            user = translateToUser(rs);
            dbHelper.jdbcUtil.close(rs, dbHelper.psmt, dbHelper.conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

//    @Override
    public int insertRegister(User user) {
        String sql = "insert into user(sno,username,password,birth,phone,mail) values(?,?,?,?,?,?)";
        List<Object> params = traToRegisterList(user);

        return dbHelper.executeOperate(sql, params);
    }

//    @Override
    public int insertUser(User user) {
        String sql = "insert into user(sno,username,password,birth,faculty,phone,mail,picture) values(?,?,?,?,?,?,?,?)";
        List<Object> params = translateToList(user);

        return dbHelper.executeOperate(sql, params);
    }

//    @Override
    public int updateUser(User user) {
        String sql = "update user set " +
                "sno = ?, " +
                "username = ?, " +
                "password = ?, " +
                "birth = ?, " +
                "faculty = ?, " +
                "phone = ?, " +
                "mail = ?, " +
                "picture = ? " +
                "where id = ?";
        List<Object> params = translateToList(user);
        params.add(user.getId());
        return dbHelper.executeOperate(sql, params);
    }

//    @Override
    public List<User> listUsers() {
        String sql = "select * from user";
        List<User> users = new ArrayList<>();
        try {
            ResultSet rs = dbHelper.executeQuery(sql);
            users = translateToUsers(rs);
            dbHelper.jdbcUtil.close(rs, dbHelper.psmt, dbHelper.conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return users;
    }


//    @Override
    public User getUser(int id) {
        String sql = "select * from user where id = ?";
        List<Object> params = new ArrayList<>();
        User user = null;
        params.add(id);
        try {
            ResultSet rs = dbHelper.executeQuery(sql, params);
            user = translateToUser(rs);
            dbHelper.jdbcUtil.close(rs, dbHelper.psmt, dbHelper.conn);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

//    @Override
    public int deleteUser(Integer id) {
        String sql = "delete from user where id = ?";
        List<Object> param = new ArrayList<>();
        param.add(id);
        return dbHelper.executeOperate(sql, param);
    }

//    @Override
    public List<UserVo> listUsersVo(int page, int limit) {
        return null;
    }

    /**
     * 一个转换的工具方法
     * @throws SQLException
     */
    private List<User> translateToUsers(ResultSet rs) throws SQLException {
        List<User> users = new ArrayList<>();
        while (rs.next()) {
            int id = rs.getInt(1);
            String sno = rs.getString(2);
            String username = rs.getString(3);
            String password = rs.getString(4);
            Date birth = rs.getDate(5);
            int faculty = rs.getInt(6);
            String phone = rs.getString(7);
            String mail = rs.getString(8);
            String picture = rs.getString(9);
            User user = new User(id, sno, username, password, birth, faculty, phone, mail, picture);
            users.add(user);
        }
        return users;
    }

    private User translateToUser(ResultSet rs) throws SQLException {
        User user = null;
        if (rs.next()) {
            int id = rs.getInt(1);
            String no = rs.getString(2);
            String username = rs.getString(3);
            String password = rs.getString(4);
            Date birth = rs.getDate(5);
            int faculty = rs.getInt(6);
            String phone = rs.getString(7);
            String mail = rs.getString(8);
            String picture = rs.getString(9);
            user = new User(id, no, username, password, birth, faculty, phone, mail, picture);
        }
        return user;
    }

    /**
     * 填充user的除id外8个基本参数
     * @return List
     */
    private List<Object> translateToList(User user) {
        List<Object> params = new ArrayList<>();
        params.add(user.getSno());
        params.add(user.getUsername());
        params.add(user.getPassword());
        params.add(user.getBirth());
        params.add(user.getFaculty());
        params.add(user.getPhone());
        params.add(user.getMail());
        params.add(user.getPicture());
        return params;
    }

    /**
     * 填充注册时使用的User
     * 仅有注册时用的学号、姓名、密码、生日、电话号码、邮箱 6个参数
     * @param user
     * @return
     */
    private List<Object> traToRegisterList(User user) {
        List<Object> params = new ArrayList<>();
        params.add(user.getSno());
        params.add(user.getUsername());
        params.add(user.getPassword());
        params.add(user.getBirth());
        params.add(user.getPhone());
        params.add(user.getMail());
        return params;
    }

}
